package hw01;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

/**
 * This class provides the implementation for the hw01 questions as described 
 * by {@link Questions}.
 * 
 * @author Joshua Belanger
 * @version 1.1
 * @see Questions
 */
public class Answers extends Questions<Integer, Integer, Integer> {
	
	private Connection connection;
	
	/**
	 * Executes functions from standard input as described in {@link Questions}.  
	 * Standard input begins with a function name followed by its arguments, one per line.
	 * 
	 * @param args
	 * @throws Exception
	 * @see Questions
	 */
    public static void main(String[] args) throws Exception {
    	Answers instance = new Answers();
    	
    	Map<String, Method> methodNameMap = new HashMap<String, Method>();
    	for(Method method : Questions.class.getDeclaredMethods()) {
    		methodNameMap.put(method.getName(), method);
    	}
    	
    	Scanner scan = new Scanner(System.in);
    	while(true) {
			String methodName = getInputLine(scan);
			if(methodName == null) {
				break;
			}
			Method method = methodNameMap.get(methodName);
    		
			List<Object> parameters = new LinkedList<Object>();
			for(Class<?> parameterType : method.getParameterTypes()) {
				String parameterInput = getInputLine(scan);
				if(parameterType.isAssignableFrom(int.class) || parameterType.isAssignableFrom(Integer.class)) {
					parameters.add(Integer.parseInt(parameterInput));
				} else if(parameterType.isAssignableFrom(String.class)) {
					parameters.add(parameterInput);
				} else if(parameterType.isAssignableFrom(Timestamp.class)) {
					parameters.add(Timestamp.valueOf(parameterInput));
				}
			}

			Object result = method.invoke(instance, parameters.toArray());
			if(result != null) {
				System.out.println(result);
			}
			System.out.print(Questions.FUNCTION_TERMINATION);
    	}
    }
    
    /**
     * Returns next input line from input.  It exludes empty lines and 
     * comments (lines begining with '#'). If no such line exists, it 
     * returns null.
     * 
     * @param scan The input scanner
     * @return Next input line for processing, null if no line exists
     */
    private static String getInputLine(Scanner scan) {
    	String inputLine = null;
    	while(scan.hasNextLine()) {
    		String line = scan.nextLine();
    		if(line.length() != 0 && line.charAt(0) != '#') {
    			inputLine = line;
    			break;
    		}
    	}
    	return inputLine;
    }
   
    @Override
    public void connect(String u, String p) throws Exception {
    	String db = u;
    	String host = "gandalf.umcs.maine.edu";
	    connection = DriverManager.getConnection("jdbc:postgresql://" + host + "/" + db, u, p);
    }
    
    @Override
    public void createTables() throws Exception {
    	PreparedStatement createTablesStatement = connection.prepareStatement(
    		//Neither work for gandalf!!!!
    		//"IF NOT EXISTS (SELECT * FROM pg_table WHERE schemaname='Rooms') THEN " +
    		//"CREATE TABLE Rooms IF NOT EXISTS (" +
    		"CREATE SEQUENCE roomID;" + 
			"CREATE TABLE Rooms (" + 
				"id INTEGER PRIMARY KEY UNIQUE DEFAULT NEXTVAL('roomID')," + 
				"building VARCHAR(20)," + 
				"floor INTEGER," + 
				"room INTEGER," + 
				"directions VARCHAR(100)" + 
			");" + 
			"CREATE SEQUENCE facilityID;" + 
			"CREATE TABLE Facilities (" +
				"id INTEGER PRIMARY KEY UNIQUE DEFAULT NEXTVAL('facilityID')," + 
				"building VARCHAR(20)," + 
				"room INTEGER," + 
				"chairs INTEGER," + 
				"screens INTEGER," + 
				"comments TEXT" + 
			");" +
			"CREATE SEQUENCE reservationID;" + 
			"CREATE TABLE Reservations (" + 
				"cnum INTEGER PRIMARY KEY UNIQUE DEFAULT NEXTVAL('reservationID')," + 
				"rstart TIMESTAMP," + 
				"rend TIMESTAMP," + 
				"building VARCHAR(20)," + 
				"room INTEGER," + 
				"name TEXT" + 
			")"
    	);
    	createTablesStatement.execute();
    }

    @Override
    public void destroyTables() throws Exception {
    	PreparedStatement destroyTablesStatement = connection.prepareStatement(
    		"DROP TABLE Rooms;" + 
			"DROP SEQUENCE roomID;" + 
			"DROP TABLE Facilities;" +
			"DROP SEQUENCE facilityID;" + 
			"DROP TABLE Reservations;" + 
			"DROP SEQUENCE reservationID"
		);
    	destroyTablesStatement.execute();
    }

    @Override
    public Integer addRoom(final String b, final int f, final int r, final String d) throws Exception {
    	PreparedStatement roomIDSatement = connection.prepareStatement(
    		"SELECT NEXTVAL('roomID')"
    	);
    	roomIDSatement.execute();
    	ResultSet nextRoomIDResult = roomIDSatement.getResultSet();
    	nextRoomIDResult.next();
    	int roomID = nextRoomIDResult.getInt(1);
    	
    	PreparedStatement addRoomStatement = connection.prepareStatement(
			"INSERT INTO Rooms VALUES (?, ?, ?, ?, ?)"
		);
    	addRoomStatement.setInt(1, roomID);
    	addRoomStatement.setString(2, b);
    	addRoomStatement.setInt(3, f);
    	addRoomStatement.setInt(4, r);
    	addRoomStatement.setString(5, d);
    	addRoomStatement.executeUpdate();
		
		return roomID;
    }

    @Override
    public void findRoom(String s) throws Exception {
    	PreparedStatement roomStatement = connection.prepareStatement(
    		"SELECT id, building, room " +
    		"FROM Rooms " +
    		"WHERE building ILIKE ? OR room ILIKE ? " +
    		"ORDER BY building, room"
    	);
    	roomStatement.setString(1, s);
    	roomStatement.setString(2, s);
    	roomStatement.execute();
    	ResultSet roomResult = roomStatement.getResultSet();
    	while(roomResult.next()) {

	    	int id = roomResult.getInt("id");
	    	String building = roomResult.getString("building");
	    	int room = roomResult.getInt("room");
	    	
	    	System.out.println(
	    		id + SEPARATOR +
	    		building + SEPARATOR +
	    		room
	    	);
    	}
    }

    @Override
    public void describeRoom(Integer i) throws Exception {
    	PreparedStatement roomStatement = connection.prepareStatement(
    		"SELECT R.building, R.floor, R.room, F.chairs, F.screens " +
    		"FROM Rooms AS R, Facilities AS F " +
    		"WHERE R.id=? AND R.room=F.room AND R.building=F.building"
    	);
    	roomStatement.setInt(1, i);
    	roomStatement.execute();
    	ResultSet roomResult = roomStatement.getResultSet();
    	if(roomResult.next()) {
    	
	    	String building = roomResult.getString("building");
	    	int floor = roomResult.getInt("floor");
	    	int room = roomResult.getInt("room");
	    	int chairs = roomResult.getInt("chairs");
	    	int screens = roomResult.getInt("screens");
	    	
	    	System.out.println(
	    		building + SEPARATOR +
	    		floor + SEPARATOR +
	    		room + SEPARATOR +
	    		chairs + SEPARATOR +
	    		screens
	    	);
    	}
    }

    @Override
    public Integer addFacilities(String b, int r, int c, int s, String d) throws Exception {
    	PreparedStatement facilityIDStatement = connection.prepareStatement(
    		"SELECT NEXTVAL('facilityID')"
    	);
    	facilityIDStatement.execute();
    	ResultSet facilityIDResult = facilityIDStatement.getResultSet();
    	facilityIDResult.next();
    	int facilityID = facilityIDResult.getInt(1);
    	
    	PreparedStatement addRoomStatement = connection.prepareStatement(
    		"INSERT INTO Facilities VALUES (?, ?, ?, ?, ?, ?)"
    	);
    	addRoomStatement.setInt(1, facilityID);
    	addRoomStatement.setString(2, b);
    	addRoomStatement.setInt(3, r);
    	addRoomStatement.setInt(4, c);
    	addRoomStatement.setInt(5, s);
    	addRoomStatement.setString(6, d);
    	addRoomStatement.executeUpdate();
		
		return facilityID;
    }

    @Override
    public Integer addReservation(Timestamp s, Timestamp e, String b, int r, String n) throws Exception {
    	PreparedStatement reservationIDStatement = connection.prepareStatement(
    		"SELECT NEXTVAL('reservationID')"
    	);
    	reservationIDStatement.execute();
    	ResultSet reservationIDResult = reservationIDStatement.getResultSet();
    	reservationIDResult.next();
    	int reservationID = reservationIDResult.getInt(1);
    	
    	PreparedStatement addRoom = connection.prepareStatement(
			"INSERT INTO Reservations VALUES (?, ?, ?, ?, ?, ?)"
		);
    	addRoom.setInt(1, reservationID);
		addRoom.setTimestamp(2, s);
		addRoom.setTimestamp(3, e);
		addRoom.setString(4, b);
		addRoom.setInt(5, r);
		addRoom.setString(6, n);
		addRoom.executeUpdate();
		
		return reservationID;
    }

    @Override
    public void describeReservation(Integer c) throws Exception {
    	PreparedStatement reservationStatement = connection.prepareStatement(
    		"SELECT * FROM Reservations WHERE cnum=? LIMIT 1"
    	);
    	reservationStatement.setInt(1, c);
    	reservationStatement.execute();
    	ResultSet reservationResult = reservationStatement.getResultSet();
    	if(reservationResult.next()) {
    	
	    	String building = reservationResult.getString("building");
	    	int room = reservationResult.getInt("room");
	    	Timestamp rstart = (Timestamp) reservationResult.getTimestamp("rstart");
	    	Timestamp rend = (Timestamp) reservationResult.getTimestamp("rend");
	    	String name = reservationResult.getString("name");
	    	
	    	System.out.println(
	    		building + SEPARATOR +
	    		room + SEPARATOR +
	    		rstart + SEPARATOR +
	    		rend + SEPARATOR +
	    		name
	    	);
    	}
    }

    @Override
    public void makeReservations(String p, int l, String b, int r, String n) throws Exception {
		for(Timestamp t : new Cron(p, l)) {
			Timestamp e = new Timestamp(t.getTime() + l * 60000);
			System.out.println(addReservation(t, e, b, r, n));
		}
    }

    @Override
    public void matchReservation(String p, String b, int r) throws Exception {
    	List<Timestamp> matches = new LinkedList<Timestamp>();
    	Iterator<Timestamp> iter = new Cron(p).iterator();
    	String betweenClause = "";
    	if(iter.hasNext()) {
    		betweenClause += " AND (";
    	}
    	while(iter.hasNext()) {
    		Timestamp t = iter.next();
    		matches.add(t);
    		betweenClause += "(rstart <= ? AND ? <= rend)";
    		if(iter.hasNext()) {
    			betweenClause += " OR ";
    		}
		}
    	if(betweenClause.length() > 0) {
    		betweenClause += ")";
    	}

    	PreparedStatement matchStatement = connection.prepareStatement(
    		"SELECT cnum " + 
    		"FROM Reservations " +
    		"WHERE building = ? AND room = ?" + betweenClause
    	);
		matchStatement.setString(1, b);
		matchStatement.setInt(2, r);
    	iter = matches.iterator();
    	for(int i = 3; iter.hasNext(); i += 2) {
    		Timestamp t = iter.next();
    		matchStatement.setTimestamp(i, t);
    		matchStatement.setTimestamp(i + 1, t);
    	}
    	
    	matchStatement.execute();
    	ResultSet matchResult = matchStatement.getResultSet();
    	while(matchResult.next()) {
    		System.out.println(matchResult.getInt("cnum"));
    	}
    }

    @Override
    public void getFreeRoom(String p, int l, int c, int s) throws Exception {
    	List<Timestamp> matches = new LinkedList<Timestamp>();
    	Iterator<Timestamp> iter = new Cron(p).iterator();
    	String betweenClause = "";
    	if(iter.hasNext()) {
    		betweenClause += "(";
    	}
    	while(iter.hasNext()) {
    		Timestamp t = iter.next();
    		matches.add(t);
    		betweenClause += "(rstart <= ? AND ? <= rend)";
    		if(iter.hasNext()) {
    			betweenClause += " OR ";
    		}
		}
    	if(betweenClause.length() > 0) {
    		betweenClause += ")";
    	}

    	PreparedStatement matchStatement = connection.prepareStatement(
    		"SELECT building, room " + 
    		"FROM Facilities " +
    		"WHERE chairs >= ? AND screens >= ? " + 
    		"EXCEPT " +
    		"SELECT building, room " + 
    		"FROM Reservations " +
    		"WHERE " + betweenClause
    	);
    	matchStatement.setInt(1, c);
    	matchStatement.setInt(2, s);
    	iter = matches.iterator();
    	for(int i = 3; iter.hasNext(); i += 2) {
    		Timestamp t = iter.next();
			Timestamp e = new Timestamp(t.getTime() + l * 60000);
    		matchStatement.setTimestamp(i, t);
    		matchStatement.setTimestamp(i + 1, e);
    	}
    	
    	matchStatement.execute();
    	ResultSet matchResult = matchStatement.getResultSet();
    	while(matchResult.next()) {
    		String building = matchResult.getString("building");
    		int room = matchResult.getInt("room");
    		System.out.println(
    			building + SEPARATOR +
    			room
    		);
    	}
    }
    
}
